﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class Customer
    {
        public const string SQL_SELECT_CUSTOMER = "Select C.CustomerNo, C.CustomerName, C.ShortName, C.Contact, C.ContactTitle, C.Tel, C.Mobile, C.Fax, C.Address, C.ZipCode, C.BankAccount, C.Bank, C.Credit, C.CreditPeriod, C.TaxAccount, C.Region, C.CustomerType, S.SubjectName as TypeName, C.Remark, C.RecordId from Customer C left join SubjectMessage S on C.CustomerType = S.SubjectId";
        public const string SQL_WHERE = " where 1 > 2";
        public const string SQL_WHERE_RECORDID = " where C.RecordId = @RecordId";

        //读取所有的客户资料列表到DataTable中
        public DataTable GetCustomerList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_CUSTOMER, null);
        }

        //创建数据表结构
        public DataTable CustomerTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_CUSTOMER + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的客户资料
        public DataRow GetCustomerByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_CUSTOMER + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public CustomerInfo GetCustomerInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            CustomerInfo customerInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_CUSTOMER + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    customerInfo = new CustomerInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7),
                                                                dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetDecimal(12), dr.GetInt32(13), dr.GetString(14), dr.GetString(15),
                                                                dr.GetInt32(16), dr.GetString(18), dr.GetInt32(19));
                }
            }
            return customerInfo;
        }

        //检测客户编号的唯一性
        public bool IsUniqueCustomerNo(string customerNo, int recordId)
        { 
            string sqlSelect = "Select count(CustomerNo) from Customer where ltrim(rtrim(CustomerNo)) = @CustomerNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@CustomerNo", SqlDbType.VarChar, 8),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = customerNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //检测客户名称的唯一性
        public bool IsUniqueCustomerName(string customerName, int recordId)
        {
            string sqlSelect = "Select count(CustomerName) from Customer where ltrim(rtrim(CustomerName)) = @CustomerName and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@CustomerName", SqlDbType.VarChar, 200),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = customerName.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //检测客户简称的唯一性
        public bool IsUniqueShortName(string shortName, int recordId)
        {
            string sqlSelect = "Select count(ShortName) from Customer where ltrim(rtrim(ShortName)) = @ShortName and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@ShortName", SqlDbType.VarChar, 60),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = shortName.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        //将数据更新到数据库
        public void WriteToDatabase(string action, CustomerInfo customer, out int recordId)
        {
            string sql = "Exec Usp_ModifyCustomer @p_Action, @p_CustomerNo, @p_CustomerName, @p_ShortName, @p_Contact, @p_ContactTitle, @p_Tel, @p_Mobile, @p_Fax, @p_Address, @p_ZipCode, @p_BankAccount, @p_Bank, @p_Credit, @p_CreditPeriod, @p_TaxAccount, @p_Region, @p_CustomerType, @p_Remark, @p_RecordId out, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
					new SqlParameter("@p_CustomerNo", SqlDbType.VarChar, 8),
					new SqlParameter("@p_CustomerName", SqlDbType.VarChar, 200),
					new SqlParameter("@p_ShortName", SqlDbType.VarChar, 60),
					new SqlParameter("@p_Contact", SqlDbType.VarChar, 20),
					new SqlParameter("@p_ContactTitle", SqlDbType.VarChar, 20),
					new SqlParameter("@p_Tel", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Mobile", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Fax", SqlDbType.VarChar, 15),
					new SqlParameter("@p_Address", SqlDbType.VarChar, 200),
					new SqlParameter("@p_ZipCode", SqlDbType.VarChar, 6),
					new SqlParameter("@p_BankAccount", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Bank", SqlDbType.VarChar, 60),
					new SqlParameter("@p_Credit", SqlDbType.Decimal, 15),
					new SqlParameter("@p_CreditPeriod", SqlDbType.Int),
                    new SqlParameter("@p_TaxAccount", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Region", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_CustomerType", SqlDbType.Int),
                    new SqlParameter("@p_Remark", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = customer.CustomerNo.Trim();
            parms[2].Value = customer.CustomerName.Trim();
            parms[3].Value = customer.ShortName.Trim();
            parms[4].Value = customer.Contact.Trim();
            parms[5].Value = customer.ContactTitle.Trim();
            parms[6].Value = customer.Tel.Trim();
            parms[7].Value = customer.Mobile.Trim();
            parms[8].Value = customer.Fax.Trim();
            parms[9].Value = customer.Address.Trim();
            parms[10].Value = customer.ZipCode.Trim();
            parms[11].Value = customer.BankAccount.Trim();
            parms[12].Value = customer.Bank.Trim();
            parms[13].Value = customer.Credit;
            parms[14].Value = customer.CreditPeriod;
            parms[15].Value = customer.TaxAccount.Trim();
            parms[16].Value = customer.Region.Trim();
            parms[17].Value = customer.CustomerType;
            parms[18].Value = customer.Remark.Trim();
            parms[19].Value = customer.RecordId;
            parms[19].Direction = ParameterDirection.InputOutput;
            parms[20].Value = "";
            parms[20].Direction = ParameterDirection.InputOutput;
            parms[21].Value = 0;
            parms[21].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[19].Value;
        }
    }
}
